{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "9bcf26ec-fd27-4d2b-aa31-cb20e3e3f6b7",
   "metadata": {},
   "outputs": [
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'pandas'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[1], line 2\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[38;5;66;03m# 导入包\u001b[39;00m\n\u001b[1;32m----> 2\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01mpandas\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mas\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01mpd\u001b[39;00m\n\u001b[0;32m      3\u001b[0m \u001b[38;5;28;01mimport\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01mstatsmodels\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mformula\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mapi\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mas\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01msmf\u001b[39;00m\n\u001b[0;32m      4\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;21;01msklearn\u001b[39;00m\u001b[38;5;21;01m.\u001b[39;00m\u001b[38;5;21;01mmodel_selection\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m train_test_split\n",
      "\u001b[1;31mModuleNotFoundError\u001b[0m: No module named 'pandas'"
     ]
    }
   ],
   "source": [
    "# 导入包\n",
    "import pandas as pd\n",
    "import statsmodels.formula.api as smf\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sqlalchemy import create_engine\n",
    "from sklearn.metrics import r2_score, mean_squared_error\n",
    "from matplotlib import pyplot as plt\n",
    "import statsmodels.api as sm\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import pymysql\n",
    "\n",
    "db_config = {\n",
    "    'host': '111.231.14.211',\n",
    "    'user': 'tushare',\n",
    "    'password': 'root',\n",
    "    'database': 'tushare',\n",
    "    'port': 13307,          # 明确指定端口\n",
    "    'charset': 'utf8mb4'   # 添加字符集设置\n",
    "}\n",
    "engine = create_engine(\n",
    "    f\"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['database']}?charset={db_config['charset']}\"\n",
    ")\n",
    "conn = pymysql.connect(**db_config)\n",
    "chunk_size = 10000\n",
    "\n",
    "# 获取华夏银行日线数据\n",
    "df = pd.read_sql_query(\n",
    "        \"\"\"\n",
    "        SELECT d.*\n",
    "        FROM date_1 d\n",
    "        WHERE d.trade_date BETWEEN '2023-01-01' AND '2023-12-31' AND d.ts_code='000001.SZ'\n",
    "        \"\"\", \n",
    "        conn, \n",
    "        chunksize=chunk_size\n",
    "    )\n",
    "df1 = pd.concat(df, ignore_index=True)\n",
    "\n",
    "df1['zd_closes'] = round(((df1['closes'] - df1['closes'].shift(1)) / df1['closes'].shift(1)), 2)\n",
    "print(df1.head)\n",
    "\n",
    "# 处理缺失数据\n",
    "df1 = df1.dropna(subset=['zd_closes'])\n",
    "print(df1.head)\n",
    "\n",
    "ex = [ 'id', 'ts_code', 'trade_date', 'the_date', 'opens', 'high', 'low', 'closes', 'pre_closes', 'changes', 'pct_chg', 'amount']\n",
    "number = df1.select_dtypes(include=['number']).columns.to_list()\n",
    "newList = [col for col in number if col not in ex]\n",
    "\n",
    "formuls = 'zd_closes ~ ' + ' + ' .join(newList)\n",
    "res = smf.ols(formuls, data=df1).fit()\n",
    "print(res.summary())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e47aca1-f1c1-4961-a005-a2fc1eebc13e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导入包\n",
    "import pandas as pd\n",
    "import statsmodels.formula.api as smf\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sqlalchemy import create_engine\n",
    "from sklearn.metrics import r2_score, mean_squared_error\n",
    "from matplotlib import pyplot as plt\n",
    "import statsmodels.api as sm\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import pymysql\n",
    "\n",
    "db_config = {\n",
    "    'host': '111.231.14.211',\n",
    "    'user': 'tushare',\n",
    "    'password': 'root',\n",
    "    'database': 'tushare',\n",
    "    'port': 13307,          # 明确指定端口\n",
    "    'charset': 'utf8mb4'   # 添加字符集设置\n",
    "}\n",
    "engine = create_engine(\n",
    "    f\"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['database']}?charset={db_config['charset']}\"\n",
    ")\n",
    "conn = pymysql.connect(**db_config)\n",
    "chunk_size = 10000\n",
    "\n",
    "# 获取华夏银行日线数据\n",
    "df = pd.read_sql_query(\n",
    "        \"\"\"\n",
    "        SELECT d.*, m.buy_lg_vol, m.sell_lg_vol, m.buy_elg_vol, m.sell_elg_vol, m.net_mf_vol,  i.vol as i_vol, i.closes as i_closes\n",
    "        FROM date_1 d\n",
    "        join moneyflows m on d.ts_code = m.ts_code and d.trade_date = m.trade_date\n",
    "        left join index_daily i on d.trade_date = i.trade_date and i.ts_code = '000001.SH'\n",
    "        WHERE d.trade_date BETWEEN '2023-01-01' AND '2023-12-31' AND d.ts_code='002229.SZ'\n",
    "        \"\"\", \n",
    "        conn, \n",
    "        chunksize=chunk_size\n",
    "    )\n",
    "df1 = pd.concat(df, ignore_index=True)\n",
    "\n",
    "df1['zd_closes'] = round(((df1['closes'] - df1['closes'].shift(1)) / df1['closes'].shift(1)), 2)\n",
    "df1['zs_closes'] = round(((df1['i_closes'] - df1['i_closes'].shift(1)) / df1['i_closes'].shift(1)), 2)\n",
    "df1['zs_vol'] = round(((df1['i_vol'] - df1['i_vol'].shift(1)) / df1['i_vol'].shift(1)), 2)\n",
    "print(df1.head)\n",
    "\n",
    "# 处理缺失数据\n",
    "df1 = df1.dropna(subset=['zd_closes', 'zs_closes', 'zs_vol'])\n",
    "print(df1.head)\n",
    "\n",
    "ex = [ 'id', 'ts_code', 'trade_date', 'the_date', 'opens', 'high', 'low', 'closes', 'pre_closes', 'changes', 'pct_chg']\n",
    "number = df1.select_dtypes(include=['number']).columns.to_list()\n",
    "newList = [col for col in number if col not in ex]\n",
    "\n",
    "formuls = 'zd_closes ~ ' + ' + ' .join(newList)\n",
    "res = smf.ols(formuls, data=df1).fit()\n",
    "print(res.summary())\n",
    "\n",
    "plt.figure(figsize=(10, 6))\n",
    "plt.scatter(res.fittedvalues, df1['zd_closes'])\n",
    "plt.show()\n",
    "features = df1[['vol', 'amount', 'buy_lg_vol', 'sell_lg_vol', 'buy_elg_vol', 'sell_elg_vol', 'net_mf_vol']]\n",
    "correlation_matrix = features.corr()\n",
    "\n",
    "plt.figure(figsize=(10, 6))\n",
    "sns.heatmap(correlation_matrix, annot=True, cmap='coolwarm', cbar=True, fmt='.2f', linewidths=.5)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de33bbb9-9e17-489c-84b1-a16962103ad6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 导入包\n",
    "import pandas as pd\n",
    "import statsmodels.formula.api as smf\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sqlalchemy import create_engine\n",
    "from sklearn.metrics import r2_score, mean_squared_error\n",
    "from matplotlib import pyplot as plt\n",
    "import statsmodels.api as sm\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import pymysql\n",
    "db_config = {\n",
    "    'host': '111.231.14.211',\n",
    "    'user': 'tushare',\n",
    "    'password': 'root',\n",
    "    'database': 'tushare',\n",
    "    'port': 13307,          # 明确指定端口\n",
    "    'charset': 'utf8mb4'   # 添加字符集设置\n",
    "}\n",
    "engine = create_engine(\n",
    "    f\"mysql+pymysql://{db_config['user']}:{db_config['password']}@{db_config['host']}:{db_config['port']}/{db_config['database']}?charset={db_config['charset']}\"\n",
    ")\n",
    "conn = pymysql.connect(**db_config)\n",
    "chunk_size = 10000\n",
    "\n",
    "# 获取华夏银行日线数据\n",
    "df = pd.read_sql_query(\n",
    "        \"\"\"\n",
    "        SELECT d.closes, d.vol , d.amount , m.buy_sm_vol , m.sell_sm_vol , m.buy_md_vol , m.sell_md_vol , m.buy_lg_vol , m.sell_lg_vol , m.buy_elg_vol , m.sell_elg_vol \n",
    "        FROM date_1 d\n",
    "        JOIN moneyflows m \n",
    "        ON d.ts_code = m.ts_code AND d.trade_date = m.trade_date\n",
    "        WHERE d.trade_date BETWEEN '2023-02-01' AND '2023-07-01' AND d.ts_code='002229.SZ'\n",
    "        \"\"\", \n",
    "        conn, \n",
    "        chunksize=chunk_size\n",
    "    )\n",
    "df1 = pd.concat(df, ignore_index=True)\n",
    "\n",
    "df1.head()\n",
    "# 增加一列，股票的涨跌幅\n",
    "df1['zd_close'] = round(((df1['closes'] - df1['closes'].shift(1)) / df1['closes'].shift(1)), 2)\n",
    "# 处理缺失值\n",
    "df1 = df1.dropna(subset=['zd_close']).reset_index(drop=True)\n",
    "\n",
    "numeric_cols = df1.select_dtypes(include=['number']).columns.tolist()\n",
    "\n",
    "# 选择需要进行主成分分析的自变量\n",
    "X = df1[['vol', 'amount', 'buy_sm_vol', 'sell_sm_vol', 'buy_md_vol', 'sell_md_vol', 'buy_lg_vol', 'sell_lg_vol', 'buy_elg_vol', 'sell_elg_vol']]\n",
    "\n",
    "# 计算特征值和特征向量\n",
    "eigenvalues, eigenvectors = np.linalg.eig(np.cov(X, rowvar=False))\n",
    "print('累计贡献率为：',round(eigenvalues[:5].sum()/eigenvalues.sum(),4)*100,'%')\n",
    "# 选择要保留的主成分个数\n",
    "n_components = 5\n",
    "top_eigenvectors = eigenvectors[:, :n_components]\n",
    "\n",
    "# 计算主成分\n",
    "principal_components = np.dot(X, top_eigenvectors)\n",
    "\n",
    "# 将主成分添加到原数据中\n",
    "principal_components = np.dot(X, top_eigenvectors)\n",
    "data_pca = pd.concat([df1, pd.DataFrame(principal_components, \n",
    "                    columns=[f'PC{i+1}' for i in range(n_components)])], axis=1)\n",
    "\n",
    "# 添加常数列前确保数据类型正确\n",
    "X_pca = data_pca[[f'PC{i+1}' for i in range(n_components)]].copy()\n",
    "X_pca = sm.add_constant(X_pca)\n",
    "\n",
    "# 确保y的索引与X_pca一致\n",
    "y = df1['zd_close'].copy()\n",
    "\n",
    "# 构建回归模型\n",
    "model_pca = sm.OLS(y, X_pca)\n",
    "\n",
    "# 拟合模型\n",
    "result_pca = model_pca.fit()\n",
    "\n",
    "# 输出结果\n",
    "print(\"\\n回归模型结果:\")\n",
    "print(result_pca.summary())\n",
    "\n",
    "# 选择PC1, PC3, PC4作为新的自变量\n",
    "X_pca_selected = data_pca[['PC1', 'PC3', 'PC4', 'PC5']]\n",
    "\n",
    "X_pca_selected.columns = ['PC1', 'PC3', 'PC4', 'PC5']\n",
    "# 添加常数列\n",
    "X_pca_selected = sm.add_constant(X_pca_selected)\n",
    "\n",
    "# 因变量\n",
    "y = df1['zd_close'].copy()\n",
    "\n",
    "# 构建回归模型\n",
    "model_pca_selected = sm.OLS(y, X_pca_selected)\n",
    "\n",
    "# 拟合模型\n",
    "result_pca_selected = model_pca_selected.fit()\n",
    "\n",
    "# 输出回归模型结果\n",
    "print(\"\\n回归模型结果:\")\n",
    "print(result_pca_selected.summary())\n",
    "\n",
    "X_pca_selected = data_pca[['PC1', 'PC3', 'PC4', 'PC5']]\n",
    "\n",
    "X_pca_selected.columns = ['PC1', 'PC3', 'PC4', 'PC5']\n",
    "# 绘制散点图\n",
    "fig, axes = plt.subplots(nrows=1, ncols=4, figsize=(15, 5))\n",
    "\n",
    "for i, col in enumerate(X_pca_selected.columns):\n",
    "    axes[i].scatter(X_pca_selected[col], y, s=50, alpha=0.7)\n",
    "    axes[i].set_xlabel(col)\n",
    "    axes[i].set_ylabel('(y)')\n",
    "    axes[i].set_title(f'{col} ')\n",
    "\n",
    "plt.tight_layout()\n",
    "plt.show()\n",
    "\n",
    "for k in range(0,5):\n",
    "    string_y = f'CP{k+1} = '\n",
    "    i = eigenvectors[k]\n",
    "    for j in range(len(i)):\n",
    "        if i[j] > 0  :\n",
    "            string_y = string_y + f'+{round(i[j],2)}*X_{j+1}'\n",
    "        else:\n",
    "            string_y = string_y + f'{round(i[j],2)}*X_{j+1}'\n",
    "    if k!=2 and k!=4:\n",
    "        print(string_y)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c1963bd3-fc1f-4b7a-a82e-d867f63ded1b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8rc1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
